package com.example.teatea.dao;

import com.example.teatea.project.Staff;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DoFindStaff {
    public List<Staff> findStaffByName(String key){
        List<Staff> staff = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        Properties properties = new Properties();
        PreparedStatement preparedStatement = null;
        StringBuilder stringBuilder = new StringBuilder();
        if("".equals(key)){
            stringBuilder = stringBuilder.append("1=1");
        }else{
            stringBuilder.append("sName like '%").append(key).append("%'");
        }
        try {
            properties.load(new FileInputStream("src/main/resources/com/example/teatea/sql.properties"));
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String Driver = properties.getProperty("Driver");
            String url = properties.getProperty("url");
            Class.forName(Driver);
            String sql = "select sName,sSex,sPhone,sTime from staff where " + stringBuilder.toString() + " and sType = 0 and sState = 1";
            connection = DriverManager.getConnection(url,user,password);
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery(sql);
            while (resultSet.next()){
                staff.add(new Staff(resultSet.getString("sName"),
                        resultSet.getString("sSex"),
                        resultSet.getString("sPhone"),
                        resultSet.getString("sTime")));
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return staff;
    }

    public List<Staff> findStaffByPhone(String key){
        List<Staff> staff = new ArrayList<>();
        Connection connection = null;
        ResultSet resultSet = null;
        Properties properties = new Properties();
        PreparedStatement presta = null;
        StringBuilder stringBuilder = new StringBuilder();//定义查询条件字符串
        if("".equals(key)){//未输入查询关键字，显示所有数据
            stringBuilder = stringBuilder.append("1=1");
        }else{//拼接按姓名和手机号模糊查询的条件字符串
            stringBuilder.append("sPhone like '%").append(key).append("%'");
        }
        try {
            properties.load(new FileInputStream("src\\main\\resources\\com\\example\\teatea\\sql.properties"));
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String Driver = properties.getProperty("Driver");
            String url = properties.getProperty("url");
            Class.forName(Driver);
            String sql = "select sName,sSex,sPhone,sTime from staff where " + stringBuilder.toString() + " and sType = 0 and sState = 1";
            connection = DriverManager.getConnection(url,user,password);
            presta = connection.prepareStatement(sql);
            resultSet = presta.executeQuery(sql);
            while (resultSet.next()){
                staff.add(new Staff(resultSet.getString("sName"),resultSet.getString("sSex"),resultSet.getString("sPhone"),resultSet.getString("sTime")));
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return staff;
    }
}
